package com.cardmanager.pro.repository;

import java.math.BigDecimal;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.cardmanager.pro.pojo.RepaymentTaskPOJO;
@Repository
public interface RepaymentTaskPOJORepository extends JpaRepository<RepaymentTaskPOJO, Long>,JpaSpecificationExecutor<RepaymentTaskPOJO> {

	@Query("select count(*) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus=:taskStatus and repaymentTaskPOJO.version=:version")
	int queryTaskStatusCountAndVersion(@Param("userId")String userIdStr, @Param("creditCardNumber")String creditCardNumber, @Param("taskStatus")int taskStatus,@Param("version")String version);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.taskType=:taskType and repaymentTaskPOJO.taskStatus=:taskStatus and repaymentTaskPOJO.executeDateTime <=:nowTime and repaymentTaskPOJO.executeDate=:executeDate and repaymentTaskPOJO.version=:version")
	List<RepaymentTaskPOJO> findTaskTypeAndTaskStatusRepaymentTaskAndVersion(@Param("taskType")int taskType,@Param("taskStatus") int taskStatus,@Param("nowTime") String nowTime,@Param("executeDate")String executeDate,@Param("version")String version);

	@Query("select count(*) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus=:taskStatus and repaymentTaskPOJO.taskType=:taskType and repaymentTaskPOJO.version=:version")
	int queryTaskStatusAndTaskTypeCountAndVersion(@Param("userId")String userId,@Param("creditCardNumber") String creditCardNumber, @Param("taskStatus")int taskStatus, @Param("taskType")int taskType,@Param("version")String version);

	@Query("select count(*) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.orderStatus=:orderStatus and repaymentTaskPOJO.taskType=:taskType and repaymentTaskPOJO.version=:version")
	int queryOrderStatusAndTaskTypeCountAndVersion(@Param("userId")String userId, @Param("creditCardNumber")String creditCardNumber, @Param("orderStatus")int orderStatus, @Param("taskType")int taskType,@Param("version")String version);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus=:taskStatus and repaymentTaskPOJO.version=:version")
	List<RepaymentTaskPOJO> findByTaskStatusRepaymentTaskAndVersion(@Param("userId")String userId, @Param("creditCardNumber")String creditCardNumber, @Param("taskStatus")int taskStatus,@Param("version")String version,Pageable pageable);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus=:taskStatus and repaymentTaskPOJO.orderStatus=:orderStatus and repaymentTaskPOJO.version=:version")
	List<RepaymentTaskPOJO> findByTaskStatusAndOrderStatusRepaymentTaskAndVersion(@Param("userId")String userId, @Param("creditCardNumber")String creditCardNumber, @Param("taskStatus")int taskStatus,@Param("orderStatus")int orderStatus,@Param("version")String version, Pageable pageable);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.version=:version")
	List<RepaymentTaskPOJO> findByUserIdAndCreditCardNumberAndVersion(@Param("userId")String userId,@Param("creditCardNumber")String creditCardNumber,@Param("version")String version, Pageable pageable);

	RepaymentTaskPOJO findByRepaymentTaskId(String repaymentTaskId);

	RepaymentTaskPOJO findByOrderCode(String orderCode);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskType=:taskType and repaymentTaskPOJO.taskStatus=:taskStatus and repaymentTaskPOJO.version=:version")
	RepaymentTaskPOJO findByTaskTypeAndTaskStatusRepaymentTaskPOJOAndVersion(@Param("userId")String userId,@Param("creditCardNumber") String creditCardNumber,@Param("taskType")int taskType,@Param("taskStatus")int taskStatus,@Param("version")String version);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.createTime=:createTime and repaymentTaskPOJO.version=:version order by repaymentTaskPOJO.executeDateTime")
	List<RepaymentTaskPOJO> findByCreateTimeAndVersion(@Param("userId")String userId, @Param("creditCardNumber")String creditCardNumber,@Param("createTime") String createTime,@Param("version")String version);

	@Query("select count(*) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.orderStatus=:orderStatus and repaymentTaskPOJO.taskType=:taskType and repaymentTaskPOJO.version=:version")
	int findByOrderStatusAndTaskTypeCountAndVersion(@Param("userId")String userId, @Param("creditCardNumber")String creditCardNumber,@Param("orderStatus") int orderStatus, @Param("taskType")int taskType,@Param("version")String version);

	@Query("select repaymentTaskPOJO.userId,repaymentTaskPOJO.creditCardNumber,sum(repaymentTaskPOJO.amount),sum(repaymentTaskPOJO.realAmount),sum(repaymentTaskPOJO.totalServiceCharge),repaymentTaskPOJO.createTime,repaymentTaskPOJO.rate,max(repaymentTaskPOJO.taskStatus),repaymentTaskPOJO.executeDate,sum(repaymentTaskPOJO.returnServiceCharge),min(repaymentTaskPOJO.taskStatus),repaymentTaskPOJO.serviceCharge from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.version=:version group by repaymentTaskPOJO.createTime")
	List<Object[]> findByCreateTimeAndVersion(@Param("userId")String userId,@Param("creditCardNumber") String creditCardNumber,@Param("version")String version,Pageable pageable);
	
	@Query("select repaymentTaskPOJO.userId,repaymentTaskPOJO.creditCardNumber,sum(repaymentTaskPOJO.amount),sum(repaymentTaskPOJO.realAmount),sum(repaymentTaskPOJO.totalServiceCharge),repaymentTaskPOJO.createTime,repaymentTaskPOJO.rate,max(repaymentTaskPOJO.taskStatus),repaymentTaskPOJO.executeDate,sum(repaymentTaskPOJO.returnServiceCharge),min(repaymentTaskPOJO.taskStatus) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' and max(repaymentTaskPOJO.taskStatus)>=:taskStatus and repaymentTaskPOJO.version=:version group by repaymentTaskPOJO.createTime")
	List<Object[]> findByCreateTimeAndTaskStatusAndVersion(@Param("userId")String userId, @Param("creditCardNumber")String creditCardNumber,@Param("taskStatus")int taskStatus,@Param("version")String version,Pageable pageable);

	@Query("select repaymentTaskPOJO.userId,repaymentTaskPOJO.creditCardNumber,sum(repaymentTaskPOJO.amount),sum(repaymentTaskPOJO.realAmount),sum(repaymentTaskPOJO.totalServiceCharge),repaymentTaskPOJO.createTime,repaymentTaskPOJO.rate,max(repaymentTaskPOJO.taskStatus),repaymentTaskPOJO.executeDate,sum(repaymentTaskPOJO.returnServiceCharge),min(repaymentTaskPOJO.taskStatus) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.executeDate=:executeDate and repaymentTaskPOJO.version=:version group by repaymentTaskPOJO.createTime")
	List<Object[]> findByCreateTimeAndExecuteDate0AndVersion(@Param("userId")String userId, @Param("creditCardNumber")String creditCardNumber,@Param("executeDate")String executeDate,@Param("version")String version,Pageable pageable);

	@Query("select repaymentTaskPOJO.userId,repaymentTaskPOJO.creditCardNumber,sum(repaymentTaskPOJO.amount),sum(repaymentTaskPOJO.realAmount),sum(repaymentTaskPOJO.totalServiceCharge),repaymentTaskPOJO.createTime,repaymentTaskPOJO.rate,max(repaymentTaskPOJO.taskStatus),repaymentTaskPOJO.executeDate,sum(repaymentTaskPOJO.returnServiceCharge),min(repaymentTaskPOJO.taskStatus) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.executeDate>:executeDate and repaymentTaskPOJO.version=:version group by repaymentTaskPOJO.createTime")
	List<Object[]> findByCreateTimeAndExecuteDate1AndVersion(@Param("userId")String userId, @Param("creditCardNumber")String creditCardNumber, @Param("executeDate")String date,@Param("version")String version,Pageable pageable);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.version=:version order by repaymentTaskPOJO.executeDate desc")
	List<RepaymentTaskPOJO> findByExecuteDateAndVersion(@Param("userId")String userId, @Param("creditCardNumber")String creditCardNumber,@Param("version")String version);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.version=:version")
	Page<RepaymentTaskPOJO> findByUserIdAndVersion(@Param("userId")String userId,@Param("version")String version,Pageable pageable);

	@Query("select sum(repaymentTaskPOJO.realAmount) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.orderStatus=:orderStatus and repaymentTaskPOJO.version=:version")
	BigDecimal findSumRealAmountByUserIdAndCreditCardNumberAndOrderStatusAndVersion(@Param("userId")String userId,@Param("creditCardNumber") String creditCardNumber,@Param("orderStatus")int orderStatus,@Param("version")String version);

	@Query("select count(*) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus=:taskStatus and repaymentTaskPOJO.orderStatus=:orderStatus and repaymentTaskPOJO.version=:version")
	int queryTaskStatusAndOrderStatusCountAndVersion(@Param("userId")String userId, @Param("creditCardNumber")String creditCardNumber,@Param("taskStatus") int taskStatus, @Param("orderStatus")int orderStatus,@Param("version")String version);

	RepaymentTaskPOJO findByCreditCardNumberAndTaskTypeAndVersion(String creditCardNumber, int taskType,String version);

	@Query("select count(*) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.orderStatus=:orderStatus and repaymentTaskPOJO.createTime =:createTime and repaymentTaskPOJO.version=:version")
	int findByUserIdAndCreditCardNumberAndCreateTimeAndOrderStatusCountAndVersion(@Param("userId")String userId, @Param("creditCardNumber")String creditCardNumber,@Param("createTime")String createTime, @Param("orderStatus")int orderStatus,@Param("version")String version);

	List<RepaymentTaskPOJO> findByExecuteDateAndOrderStatusAndVersion(String executeDate, int orderStatus,String version);

	RepaymentTaskPOJO findByUserIdAndCreditCardNumberAndTaskStatusAndTaskTypeAndOrderStatusAndVersion(String userId,String creditCardNumber, int taskStatus, int taskType, int orderStatus, String version);
	
	@Query("select repaymentTaskPOJO.userId,repaymentTaskPOJO.creditCardNumber,sum(repaymentTaskPOJO.amount),sum(repaymentTaskPOJO.realAmount),sum(repaymentTaskPOJO.totalServiceCharge),repaymentTaskPOJO.createTime,repaymentTaskPOJO.rate,max(repaymentTaskPOJO.taskStatus),repaymentTaskPOJO.executeDate,sum(repaymentTaskPOJO.returnServiceCharge),min(repaymentTaskPOJO.taskStatus),repaymentTaskPOJO.serviceCharge from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.version=:version group by repaymentTaskPOJO.createTime")
	List<Object[]> findByCreateTimeAndVersion(@Param("userId")String userId, @Param("version")String version, Pageable pageable);

	List<RepaymentTaskPOJO> findByTaskTypeAndOrderStatusAndVersion(int taskType, int orderStatus, String version);

	List<RepaymentTaskPOJO> findByTaskTypeAndTaskStatusAndVersionAndExecuteDate(int taskType, int taskStatus,
			String version, String e);

}
